﻿//2011-01-11    Initial                     By 應翰

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

//Using Namespace
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration ;

namespace DataAccess
{
    public class DBAccess_CHTD
    {
        string strSQL;
        DataTable dtTemp;
        string strUpdateTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
        string _ErrMsg = string.Empty;
        private string myConnectionString = ConfigurationManager.AppSettings["strDSN2"];
        private SqlConnection myConnection;
        private SqlTransaction transaction;
        private SqlCommand mycom;

        public string ErrMsg
        {
            get { return _ErrMsg; }
        }

        public enum OperatorType
        {
            EveryDay,
            MondayToFriday,
            SaturdayToSunday,
            Saturday,
            Sunday
        }

        public enum ScheduleType
        {
            Device,
            Group
        }

        public DataTable getOrgGroupTable(string customerId)
        {
            string strSQL = "select * from c_org_group where _customer_id='" + customerId + "'";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }


        public bool updateUserData(string enable, string name, string type,
                                        string account, string password, string phoneOffice,
                                        string phoneMobile, string email)
        {
            string strSQL = "UPDATE c_user SET _enable='" + enable + "'" +
                            ", _name='" + name + "'" +
                            ", _type='" + type + "'" +
                            ", _password='" + password + "'" +
                            ", _phone_office='" + phoneOffice + "'" +
                            ", _phone_mobile='" + phoneMobile + "'" +
                            ", _mail='" + email + "'" +
                            " WHERE _account='" + account + "'";

            return new CommonFun().ExeSQL(strSQL);
        }

        public bool deleteUserData(string account)
        {
            string strSQL = "DELETE From c_user WHERE _account='" + account + "'";

            return new CommonFun().ExeSQL(strSQL);
        }

        public bool createUserData(string enable, string name, string type,
                                        string account, string password, string phoneOffice,
                                        string phoneMobile, string email)
        {
            string strSQL = "INSERT INTO c_user (_customer_id, _enable, _name, _type, _account, _password, _phone_office, _phone_mobile, _mail) VALUES ('1'" +
                                                ", '" + enable + "'" +
                                                ", '" + name + "'" +
                                                ", '" + type + "'" +
                                                ", '" + account + "'" +
                                                ", '" + password + "'" +
                                                ", '" + phoneOffice + "'" +
                                                ", '" + phoneMobile + "'" +
                                                ", '" + email + "')";

            return new CommonFun().ExeSQL(strSQL);
        }

        ///<summary>
        /// 查詢有平均需量Tag的電錶 By scent at 2011/8/1
        ///</summary>
        public DataTable GetPowerMeterWithDemand(int Para_FloorID)
        {
            DataTable dt = new DataTable();

            try
            {
                strSQL = "SELECT _id,_customer_id,_ctrl_server_id,_controller_id,_org_group_id,_ref_id,_name FROM c_device";
                strSQL = strSQL + " WHERE _org_group_id IN (SELECT _id FROM c_org_group WHERE _parent_id = " + Para_FloorID + " and _type = 'BLOCK')";
                strSQL = strSQL + " AND _id IN (SELECT _device_id FROM c_tag WHERE _type = 2)";

                dt = new CommonFun().GenDataTable2(strSQL);

                _ErrMsg = string.Empty;
            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }

            return dt;
        }

         ///<summary>
        /// 指定BlockID，取得底下計費電錶設備 By scent at 2011/8/4
        ///</summary>
        public DataTable GetPowerMeter4Fee(int Para_BlockID)
        {
            //計費電錶_subtype = 1
            DataTable dt = new DataTable();

            try
            {
                string strSQL = "SELECT _id as DeviceID,_name as DeviceName FROM c_device WHERE (_subtype = 1) AND (_org_group_id = " + Para_BlockID + ")";
                dt = new CommonFun().GenDataTable2(strSQL);
            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }
            
            return dt;
        }

        ///<summary>
        /// 取得目前可以加入排程的電錶 By scent at 2011/8/10
        ///</summary>
        public DataTable GetPowerMeter4Schedule()
        {
            DataTable dt = new DataTable();

            try
            {
                string strSQL = "SELECT _id as DeviceID,_name as DeviceName FROM c_device WHERE _id IN (SELECT DISTINCT _device_id FROM c_tag WHERE _name = '開關設定') AND _id NOT IN (SELECT DISTINCT _device_id FROM c_devicegroup_body)";
                dt = new CommonFun().GenDataTable2(strSQL);
            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }

            return dt;
        }

        ///<summary>
        /// 取得可以加入排程的電錶 By scent at 2011/8/17
        ///</summary>
        public DataTable GetPowerMeterSupportSchedule()
        {
            DataTable dt = new DataTable();

            try
            {
                string strSQL = "SELECT _id as DeviceID,_name as DeviceName FROM c_device WHERE _id IN (SELECT DISTINCT _device_id FROM c_tag WHERE _name = '開關設定')";
                dt = new CommonFun().GenDataTable2(strSQL);
            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }

            return dt;
        }

        ///<summary>
        /// 取得已經加入電錶群組的電錶ID By scent at 2011/8/10
        ///</summary>
        public DataTable GetPowerMeterByGroupID(int Para_GroupID)
        {
            DataTable dt = new DataTable();

            try
            {
                string strSQL = "SELECT _id as DeviceID,_name as DeviceName FROM c_device WHERE _id IN (SELECT DISTINCT _device_id FROM c_devicegroup_body WHERE _devicegroup_id = " + Para_GroupID + ")";
                dt = new CommonFun().GenDataTable2(strSQL);
            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }

            return dt;
        }

        ///<summary>
        /// 刪除指定電錶群組資訊 By scent at 2011/8/10
        ///</summary>
        public bool DeleteGroupInformationByGroupID(int Para_GroupID)
        {
            bool Sucess = true;
            string strSQL = string.Empty;
            string _schedule_id = string.Empty;
            string _schedule_group_id = string.Empty;

            #region 先取得c_schedule的_id與_schedule_group_id
            DataTable dt = new DataTable();
            strSQL = "SELECT _id,_schedule_group_id FROM c_schedule WHERE _schedule_group_id IN (SELECT DISTINCT _schedule_group_id FROM c_schedule_mapping WHERE (_type=1) AND (_typeID = " + Para_GroupID + "))";
            dt = new CommonFun().GenDataTable2(strSQL);

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    _schedule_id = _schedule_id + "," + Convert.ToString(row["_id"]);
                    _schedule_group_id = _schedule_group_id + "," + Convert.ToString(row["_schedule_group_id"]);
                }

                _schedule_id = _schedule_id.Substring(1);
                _schedule_group_id = _schedule_group_id.Substring(1);
            }
            else
            {
                _schedule_group_id = "0";
                _schedule_id = "0";
            }
            #endregion

            myConnection = new SqlConnection(myConnectionString);
            myConnection.Open();
            transaction = myConnection.BeginTransaction();

            try
            {
                #region 刪除c_devicegroup_body
                strSQL = "DELETE FROM c_devicegroup_body WHERE _devicegroup_id = @_devicegroup_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_devicegroup_id", SqlDbType.Int).Value = Para_GroupID;

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 刪除c_devicegroup_head
                strSQL = "DELETE FROM c_devicegroup_head WHERE _id = @_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_id", SqlDbType.Int).Value = Para_GroupID;

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 刪除c_schedule_action
                strSQL = "DELETE FROM c_schedule_action WHERE _schedule_id IN (" + _schedule_id + ")";
                mycom = new SqlCommand(strSQL, myConnection);

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 刪除c_schedule
                strSQL = "DELETE FROM c_schedule WHERE _id IN (" + _schedule_id + ")";
                mycom = new SqlCommand(strSQL, myConnection);

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 刪除c_schedule_mapping
                strSQL = "DELETE FROM c_schedule_mapping WHERE _schedule_group_id IN (" + _schedule_group_id + ")";
                mycom = new SqlCommand(strSQL, myConnection);

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 刪除c_schedule_group
                strSQL = "DELETE FROM c_schedule_group WHERE _id IN (" + _schedule_group_id + ")";
                mycom = new SqlCommand(strSQL, myConnection);

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                transaction.Commit();

            }
            catch (Exception Ex)
            {
                transaction.Rollback();
                _ErrMsg = Ex.Message;
                Sucess = false;
            }
            finally
            {
                myConnection.Close();
            }

            return Sucess;
        }


        ///<summary>
        /// 更新指定電錶群組資訊 By scent at 2011/8/15
        ///</summary>
        public bool UpdateGroupInformationByGroupID(int Para_GroupID, string Para_GroupName, int[] Para_DeviceIDs)
        {
            bool Sucess = true;
            string strSQL = string.Empty;
            DataTable dtDevice = new DataTable();
            DataTable dtSchedule = new DataTable();

            myConnection = new SqlConnection(myConnectionString);
            myConnection.Open();
            transaction = myConnection.BeginTransaction();

            try
            {
                #region 更新c_devicegroup_head
                strSQL = "UPDATE c_devicegroup_head SET _name = @_name WHERE _id = @_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_id", SqlDbType.Int).Value = Para_GroupID;
                mycom.Parameters.Add("@_name", SqlDbType.NVarChar, 256).Value = Para_GroupName;

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 刪除c_devicegroup_body
                strSQL = "DELETE FROM c_devicegroup_body WHERE _devicegroup_id = @_devicegroup_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_devicegroup_id", SqlDbType.Int).Value = Para_GroupID;

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 新增c_devicegroup_body
                
                foreach (int DeviceID in Para_DeviceIDs)
                {
                    strSQL = "INSERT INTO c_devicegroup_body (_devicegroup_id,_device_id) VALUES (@_devicegroup_id,@_device_id)";
                    mycom = new SqlCommand(strSQL, myConnection);
                    mycom.Parameters.Add("@_devicegroup_id", SqlDbType.Int).Value = Para_GroupID;
                    mycom.Parameters.Add("@_device_id", SqlDbType.Int).Value = DeviceID;

                    mycom.Transaction = transaction;
                    mycom.ExecuteNonQuery();
                }

                #endregion

                #region 檢查此群組是否有設定排程
                strSQL = "SELECT _id FROM c_schedule WHERE _schedule_group_id IN ";
                strSQL = strSQL + "(SELECT DISTINCT _schedule_group_id FROM c_schedule_mapping WHERE _type = 1 AND _typeID = " + Para_GroupID + ")";
                dtSchedule = new CommonFun().GenDataTable2(strSQL);

                #endregion

                if (dtSchedule.Rows.Count > 0)
                {

                    #region 刪除有關此群組的排程資料
                    strSQL = "DELETE FROM c_schedule_action WHERE _schedule_id IN ";
                    strSQL = strSQL + "(SELECT _id FROM c_schedule WHERE _schedule_group_id IN ";
                    strSQL = strSQL + "(SELECT DISTINCT _schedule_group_id FROM c_schedule_mapping WHERE _type = 1 AND _typeID = @_typeID)) ";
                    mycom = new SqlCommand(strSQL, myConnection);
                    mycom.Parameters.Add("@_typeID", SqlDbType.Int).Value = Para_GroupID;

                    mycom.Transaction = transaction;
                    mycom.ExecuteNonQuery();
                    #endregion

                    #region 取得設備基本資訊
                    string Para_ID = string.Empty;
                    foreach (int DeviceID in Para_DeviceIDs)
                    {
                        Para_ID = Para_ID + "," + DeviceID;
                    }
                    Para_ID = Para_ID.Substring(1);

                    strSQL = "SELECT T1.*,T2.TagID FROM ";
                    strSQL = strSQL + "(SELECT _id,_customer_id,_ctrl_server_id,_controller_id,_org_group_id,_ref_id,_name FROM c_device WHERE _id = " + Para_ID + ") as T1 ";
                    strSQL = strSQL + "LEFT JOIN ";
                    strSQL = strSQL + "(SELECT _id as TagID,_device_id FROM c_tag WHERE _name= '開關設定') as T2 ";
                    strSQL = strSQL + "ON T1._id = T2._device_id";
                    dtDevice = new CommonFun().GenDataTable2(strSQL);
                    #endregion

                    #region 插入更新後的群組排程資訊
                    
                    foreach (DataRow row in dtSchedule.Rows)
                    {
                        int _schedule_id = Convert.ToInt32(row["_id"]);

                        foreach (DataRow DeviceRow in dtDevice.Rows)
                        {
                            strSQL = "INSERT INTO c_schedule_action (_schedule_id,_enabled,_type,_controller_id,_device_id,_tag_id,_load_tag_value,_unload_tag_value,_cron,_priority) ";
                            strSQL = strSQL + "VALUES (@_schedule_id,@_enabled,@_type,@_controller_id,@_device_id,@_tag_id,@_load_tag_value,@_unload_tag_value,@_cron,@_priority)";

                            mycom = new SqlCommand(strSQL, myConnection);
                            mycom.Parameters.Add("@_schedule_id", SqlDbType.Int).Value = _schedule_id;
                            mycom.Parameters.Add("@_enabled", SqlDbType.Bit).Value = '1';
                            mycom.Parameters.Add("@_type", SqlDbType.Int).Value = 0;
                            mycom.Parameters.Add("@_controller_id", SqlDbType.Int).Value = Convert.ToInt32(DeviceRow["_controller_id"]);
                            mycom.Parameters.Add("@_device_id", SqlDbType.Int).Value = Convert.ToInt32(DeviceRow["_id"]);
                            mycom.Parameters.Add("@_tag_id", SqlDbType.Int).Value = Convert.ToInt32(DeviceRow["TagID"]);
                            mycom.Parameters.Add("@_load_tag_value", SqlDbType.Float).Value = 0;
                            mycom.Parameters.Add("@_unload_tag_value", SqlDbType.Float).Value = 1;
                            mycom.Parameters.Add("@_cron", SqlDbType.NVarChar, 128).Value = "*;*;*;*;*;*";
                            mycom.Parameters.Add("@_priority", SqlDbType.Int).Value = 0;

                            mycom.Transaction = transaction;
                            mycom.ExecuteNonQuery();
                        }
                    }

                    #endregion
                }

                transaction.Commit();

            }
            catch (Exception Ex)
            {
                transaction.Rollback();
                _ErrMsg = Ex.Message;
                Sucess = false;
            }
            finally
            {
                myConnection.Close();
            }

            return Sucess;
        }

        ///<summary>
        /// 插入指定電錶群組資訊 By scent at 2011/8/15
        ///</summary>
        public bool InsertGroupInformationByGroupID(string Para_GroupName, int[] Para_DeviceIDs)
        {
            bool Sucess = true;
            string strSQL = string.Empty;

            myConnection = new SqlConnection(myConnectionString);
            myConnection.Open();
            transaction = myConnection.BeginTransaction();

            try
            {
                int GroupID;

                #region 插入c_devicegroup_head

                strSQL = "INSERT INTO c_devicegroup_head (_name) VALUES (@_name) SET @_id = SCOPE_IDENTITY()";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_name", SqlDbType.NVarChar,256).Value = Para_GroupName;
                SqlParameter _id = new SqlParameter("@_id", SqlDbType.Int);
                _id.Direction = ParameterDirection.Output;
                mycom.Parameters.Add(_id);

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                
                GroupID = Convert.ToInt32(_id.Value);

                #endregion

                #region 插入c_devicegroup_body
                
                foreach (int DeviceID in Para_DeviceIDs)
                {
                    strSQL = "INSERT INTO c_devicegroup_body (_devicegroup_id,_device_id) VALUES (@_devicegroup_id,@_device_id)";
                    mycom = new SqlCommand(strSQL, myConnection);
                    mycom.Parameters.Add("@_devicegroup_id", SqlDbType.Int).Value = GroupID;
                    mycom.Parameters.Add("@_device_id", SqlDbType.Int).Value = DeviceID;

                    mycom.Transaction = transaction;
                    mycom.ExecuteNonQuery();
                }

                #endregion

                transaction.Commit();
            }
            catch (Exception Ex)
            {
                transaction.Rollback();
                _ErrMsg = Ex.Message;
                Sucess = false;
            }
            finally
            {
                myConnection.Close();
            }

            return Sucess;
        }

        ///<summary>
        /// 插入排程資訊 By scent at 2011/8/15
        ///</summary>
        public bool InsertSchedule(ScheduleType Para_ScheduleType, int Para_ID,string Para_ScheduleName, DateTime Para_STime, DateTime Para_ETime, int StartTime_HH, int StartTime_mm, int StopTime_HH, int StopTime_mm, OperatorType Para_OperatorType)
        {
            bool Sucess = true;
            string strSQL = string.Empty;

            myConnection = new SqlConnection(myConnectionString);
            myConnection.Open();
            transaction = myConnection.BeginTransaction();

            try
            {
                DataTable dt = new DataTable();
                int  schedule_group_id ;
                int schedule_id;

                #region 取得設備基本資訊
                
                if (Para_ScheduleType == ScheduleType.Device)
                {
                    strSQL = "SELECT T1.*,T2.TagID FROM ";
                    strSQL = strSQL + "(SELECT _id,_customer_id,_ctrl_server_id,_controller_id,_org_group_id,_ref_id,_name FROM c_device WHERE _id = " + Para_ID + ") as T1 ";
                    strSQL = strSQL + "LEFT JOIN ";
                    strSQL = strSQL + "(SELECT _id as TagID,_device_id FROM c_tag WHERE _name= '開關設定') as T2 ";
                    strSQL = strSQL + "ON T1._id = T2._device_id";
                    dt = new CommonFun().GenDataTable2(strSQL);
                }
                else
                {
                    strSQL = "SELECT T1.*,T2.TagID FROM ";
                    strSQL = strSQL + "(SELECT _id,_customer_id,_ctrl_server_id,_controller_id,_org_group_id,_ref_id,_name FROM c_device WHERE _id IN (SELECT DISTINCT _device_id FROM c_devicegroup_body WHERE _devicegroup_id = " + Para_ID + ")) as T1 ";
                    strSQL = strSQL + "LEFT JOIN ";
                    strSQL = strSQL + "(SELECT _id as TagID,_device_id FROM c_tag WHERE _name= '開關設定') as T2 ";
                    strSQL = strSQL + "ON T1._id = T2._device_id";
                    dt = new CommonFun().GenDataTable2(strSQL);
                }

                if (dt.Rows.Count == 0)
                    throw new Exception("無設備資訊，無法加入排程");
                #endregion

                #region 插入c_schedule_group

                strSQL = "INSERT INTO c_schedule_group (_enabled,_customer_id,_status,_name,_start,_end,_cron) ";
                strSQL = strSQL + "VALUES (@_enabled,@_customer_id,@_status,@_name,@_start,@_end,@_cron) SET @_id = SCOPE_IDENTITY()";
                
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_enabled", SqlDbType.Bit).Value = true;
                mycom.Parameters.Add("@_customer_id", SqlDbType.Int).Value = Convert.ToInt32(dt.Rows[0]["_customer_id"]);
                mycom.Parameters.Add("@_status", SqlDbType.Int).Value = 0;
                mycom.Parameters.Add("@_name", SqlDbType.NVarChar, 256).Value = Para_ScheduleName;
                mycom.Parameters.Add("@_start", SqlDbType.DateTime).Value = Para_STime;
                mycom.Parameters.Add("@_end", SqlDbType.DateTime).Value = Para_ETime;
                mycom.Parameters.Add("@_cron", SqlDbType.NVarChar, 128).Value = Make_CronCommand(StartTime_HH,StartTime_mm,StopTime_HH,StopTime_mm,Para_OperatorType) ;

                if (_ErrMsg != string.Empty)
                    throw new Exception(_ErrMsg);
                  
                SqlParameter _id = new SqlParameter("@_id", SqlDbType.Int);
                _id.Direction = ParameterDirection.Output;
                mycom.Parameters.Add(_id);

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                schedule_group_id = Convert.ToInt32(_id.Value);

                #endregion

                #region 插入c_schedule

                strSQL = "INSERT INTO c_schedule (_enabled,_schedule_group_id,_name) ";
                strSQL = strSQL + "VALUES (@_enabled,@_schedule_group_id,@_name) SET @_id = SCOPE_IDENTITY()";
                
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_enabled", SqlDbType.Bit).Value = true;
                mycom.Parameters.Add("@_schedule_group_id", SqlDbType.Int).Value = schedule_group_id;
                mycom.Parameters.Add("@_name", SqlDbType.NVarChar, 256).Value = Para_ScheduleName;
                
                SqlParameter _id2 = new SqlParameter("@_id", SqlDbType.Int);
                _id2.Direction = ParameterDirection.Output;
                mycom.Parameters.Add(_id2);

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                schedule_id = Convert.ToInt32(_id2.Value);

                #endregion

                #region 插入c_schedule_action

                foreach (DataRow row in dt.Rows)
                {
                    strSQL = "INSERT INTO c_schedule_action (_schedule_id,_enabled,_type,_controller_id,_device_id,_tag_id,_load_tag_value,_unload_tag_value,_cron,_priority) ";
                    strSQL = strSQL + "VALUES (@_schedule_id,@_enabled,@_type,@_controller_id,@_device_id,@_tag_id,@_load_tag_value,@_unload_tag_value,@_cron,@_priority)";

                    mycom = new SqlCommand(strSQL, myConnection);
                    mycom.Parameters.Add("@_schedule_id", SqlDbType.Int).Value = schedule_id;
                    mycom.Parameters.Add("@_enabled", SqlDbType.Bit).Value = true;
                    mycom.Parameters.Add("@_type", SqlDbType.Int).Value = 0;
                    mycom.Parameters.Add("@_controller_id", SqlDbType.Int).Value = Convert.ToInt32(row["_controller_id"]);
                    mycom.Parameters.Add("@_device_id", SqlDbType.Int).Value = Convert.ToInt32(row["_id"]);
                    mycom.Parameters.Add("@_tag_id", SqlDbType.Int).Value = Convert.ToInt32(row["TagID"]);
                    mycom.Parameters.Add("@_load_tag_value", SqlDbType.Float).Value = 0;
                    mycom.Parameters.Add("@_unload_tag_value", SqlDbType.Float).Value = 1;
                    mycom.Parameters.Add("@_cron", SqlDbType.NVarChar, 128).Value = "*;*;*;*;*;*";
                    mycom.Parameters.Add("@_priority", SqlDbType.Int).Value = 0;

                    mycom.Transaction = transaction;
                    mycom.ExecuteNonQuery();
                }
                #endregion

                #region 插入c_schedule_mapping
                strSQL = "INSERT INTO c_schedule_mapping (_type,_typeID,_schedule_group_id) ";
                strSQL = strSQL + "VALUES (@_type,@_typeID,@_schedule_group_id)";

                mycom = new SqlCommand(strSQL, myConnection);
                if (Para_ScheduleType == ScheduleType.Device)
                    mycom.Parameters.Add("@_type", SqlDbType.Int).Value = 0;
                else
                    mycom.Parameters.Add("@_type", SqlDbType.Int).Value = 1;

                mycom.Parameters.Add("@_typeID", SqlDbType.Int).Value = Para_ID;
                mycom.Parameters.Add("@_schedule_group_id", SqlDbType.Int).Value = schedule_group_id;
                
                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                transaction.Commit();

            }
            catch (Exception Ex)
            {
                transaction.Rollback();
                _ErrMsg = Ex.Message;
                Sucess = false;
            }
            finally
            {
                myConnection.Close();
            }

            return Sucess;
        }

        ///<summary>
        /// 刪除排程資訊 By scent at 2011/8/15
        ///</summary>
        public bool DeleteSchedule(int Para_schedule_group_id, int Para_schedule_id)
        {
            bool Sucess = true;
            string strSQL = string.Empty;

            myConnection = new SqlConnection(myConnectionString);
            myConnection.Open();
            transaction = myConnection.BeginTransaction();

            try
            {
                #region 刪除c_schedule_action
                strSQL = "DELETE FROM c_schedule_action WHERE _schedule_id = @_schedule_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_schedule_id", SqlDbType.Int).Value = Para_schedule_id;
                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 刪除c_schedule
                strSQL = "DELETE FROM c_schedule WHERE _id = @_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_id", SqlDbType.Int).Value = Para_schedule_id;
                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 刪除c_schedule_mapping
                strSQL = "DELETE FROM c_schedule_mapping WHERE _schedule_group_id = @_schedule_group_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_schedule_group_id", SqlDbType.Int).Value = Para_schedule_group_id;
                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 刪除c_schedule_group
                strSQL = "DELETE FROM c_schedule_group WHERE _id = @_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_id", SqlDbType.Int).Value = Para_schedule_group_id;
                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                transaction.Commit();
            }
            catch (Exception Ex)
            {
                transaction.Rollback();
                _ErrMsg = Ex.Message;
                Sucess = false;
            }
            finally
            {
                myConnection.Close();
            }

            return Sucess;
        }

        ///<summary>
        /// 取得排程資訊 By scent at 2011/8/15
        ///</summary>
        public DataTable GetScheduleInformation(ScheduleType Para_ScheduleType, int Para_ID)
        {
            DataTable dt = new DataTable();
            string strSQL = string.Empty;

            try
            {
                string strWHERE = string.Empty;

                if (Para_ScheduleType == ScheduleType.Device)
                    strWHERE ="(_type=0) AND (_typeID = " + Para_ID + ")";
                else
                    strWHERE = "(_type=1) AND (_typeID = " + Para_ID + ")";

                strSQL = "SELECT T.*,c_schedule._id as '_schedule_id','' as 'PowerStartTime','' as 'PowerStopTime'";
                strSQL = strSQL + " FROM";
                strSQL = strSQL + " (";
                strSQL = strSQL + " SELECT _id as '_schedule_group_id',_name,_start,_end,_cron";
                strSQL = strSQL + " FROM c_schedule_group WHERE _id IN (SELECT _schedule_group_id FROM c_schedule_mapping WHERE " + strWHERE + ")";
                strSQL = strSQL + " ) as T,c_schedule";
                strSQL = strSQL + " WHERE (T._schedule_group_id = c_schedule._schedule_group_id)";
                
                dt = new CommonFun().GenDataTable2(strSQL);

                foreach (DataRow row in dt.Rows)
                {
                    string _cron = Convert.ToString(row["_cron"]) ;
                    if ( _cron != string.Empty)
                    {
                        string[] ArrayMotion = _cron.Split(new char[] {' '});

                        if (ArrayMotion.Length == 1)
                        {
                            string[] CronItem = ArrayMotion[0].Split(new char[] { ';' });

                            #region 運轉方式
                            switch (CronItem[3])
                            {
                                case "*":
                                    row["_cron"] = "每天" ;
                                    break;
                                case "1-5":
                                    row["_cron"] = "星期一至星期五" ;
                                    break;
                                case "6":
                                    row["_cron"] = "星期六" ;
                                    break;
                                case "6-7":
                                    row["_cron"] = "星期六至星期日" ;
                                    break;
                                case "7":
                                    row["_cron"] = "星期日" ;
                                    break ;
                            }
                            #endregion

                            #region 供電時間 & 斷電時間
                            
                            string[] temp = CronItem[5].Split(new char[] { '-' }) ;
                            row["PowerStartTime"] = CronItem[4] + "時" + temp[0] + "分";
                            row["PowerStopTime"] = CronItem[4] + "時" + temp[1] + "分";

                            #endregion
                        }
                        else
                        {
                            string[] CronItem = ArrayMotion[0].Split(new char[] { ';' });
                            string[] CronItem2 = ArrayMotion[ArrayMotion.Length - 1].Split(new char[] { ';' });

                            #region 運轉方式
                            switch (CronItem[3])
                            {
                                case "*":
                                    row["_cron"] = "每天";
                                    break;
                                case "1-5":
                                    row["_cron"] = "星期一至星期五";
                                    break;
                                case "6":
                                    row["_cron"] = "星期六";
                                    break;
                                case "6-7":
                                    row["_cron"] = "星期六至星期日";
                                    break;
                                case "7":
                                    row["_cron"] = "星期日";
                                    break;
                            }
                            #endregion

                            #region 供電時間

                            string[] temp = CronItem[5].Split(new char[] { '-' });
                            row["PowerStartTime"] = CronItem[4] + "時" + temp[0] + "分";
                            
                            #endregion

                            #region 斷電時間

                            string[] temp2 = CronItem2[5].Split(new char[] { '-' });
                            row["PowerStopTime"] = CronItem2[4] + "時" + temp2[1] + "分";

                            #endregion

                        }
                    }
                }
            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }

            return dt;
        }

        ///<summary>
        /// 更新排程資訊 By scent at 2011/8/17
        ///</summary>
        public bool UpdateSchedule(int Para_schedule_group_id, int Para_schedule_id, string Para_ScheduleName, DateTime Para_STime, DateTime Para_ETime, int StartTime_HH, int StartTime_mm, int StopTime_HH, int StopTime_mm, OperatorType Para_OperatorType)
        {
            bool Sucess = true;
            string strSQL = string.Empty;

            myConnection = new SqlConnection(myConnectionString);
            myConnection.Open();
            transaction = myConnection.BeginTransaction();

            try
            {
                #region 更新c_schedule
                strSQL = "UPDATE c_schedule SET _name = @_name WHERE _id = @_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_id", SqlDbType.Int).Value = Para_schedule_id;
                mycom.Parameters.Add("@_name", SqlDbType.NVarChar, 256).Value = Para_ScheduleName; 

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();
                #endregion

                #region 更新c_schedule_group
                strSQL = "UPDATE c_schedule_group SET _name = @_name,_start = @_start,_end = @_end,_cron = @_cron WHERE _id = @_id";
                mycom = new SqlCommand(strSQL, myConnection);
                mycom.Parameters.Add("@_id", SqlDbType.Int).Value = Para_schedule_group_id;
                mycom.Parameters.Add("@_name", SqlDbType.NVarChar, 256).Value = Para_ScheduleName;
                mycom.Parameters.Add("@_start", SqlDbType.DateTime).Value = Para_STime;
                mycom.Parameters.Add("@_end", SqlDbType.DateTime).Value = Para_ETime;
                mycom.Parameters.Add("@_cron", SqlDbType.NVarChar, 128).Value = Make_CronCommand(StartTime_HH, StartTime_mm, StopTime_HH, StopTime_mm, Para_OperatorType);

                if (_ErrMsg != string.Empty)
                    throw new Exception(_ErrMsg);

                mycom.Transaction = transaction;
                mycom.ExecuteNonQuery();

                #endregion

                transaction.Commit();
            }
            catch (Exception Ex)
            {
                transaction.Rollback();
                _ErrMsg = Ex.Message;
                Sucess = false;
            }
            finally
            {
                myConnection.Close();
            }

            return Sucess;
        }

        ///<summary>
        /// 用來製作排程指令(Cron Command) By scent at 2011/8/17
        ///</summary>
        private string Make_CronCommand(int StartTime_HH, int StartTime_mm, int StopTime_HH, int StopTime_mm, OperatorType Para_OperatorType)
        {
            string Para_cron = "*;*;*";
            string CronCommand = string.Empty;

            try
            {
                
                switch (Para_OperatorType)
                {
                    case OperatorType.EveryDay:
                        Para_cron = Para_cron + ";*";
                        break;
                    case OperatorType.MondayToFriday:
                        Para_cron = Para_cron + ";1-5";
                        break;
                    case OperatorType.Saturday:
                        Para_cron = Para_cron + ";6";
                        break;
                    case OperatorType.SaturdayToSunday:
                        Para_cron = Para_cron + ";6-7";
                        break;
                    case OperatorType.Sunday:
                        Para_cron = Para_cron + ";7";
                        break;
                }

                if (StopTime_HH >= StartTime_HH) //Ex:時間上2:00~17:00
                {
                    int SubHour = StopTime_HH - StartTime_HH;

                    if (SubHour == 0)
                    {
                        CronCommand = CronCommand + Para_cron + ";" + StartTime_HH + ";" + StartTime_mm + "-" + StopTime_mm;
                    }
                    else
                    {
                        string First = string.Empty;
                        string Middle = string.Empty;
                        string Last = string.Empty;

                        for (int i = 0; i <= SubHour; i++)
                        {
                            if ((StartTime_HH + i) == StartTime_HH)
                            {
                                //CronCommand = CronCommand + Para_cron + ";" + (StartTime_HH + i) + ";" + StartTime_mm + "-59";
                                First = Para_cron + ";" + (StartTime_HH + i) + ";" + StartTime_mm + "-59";
                                continue;
                            }
                            else if ((StartTime_HH + i) == StopTime_HH)
                            {
                                //CronCommand = CronCommand + " " + Para_cron + ";" + (StartTime_HH + i) + ";" + "0-" + StopTime_mm;
                                Last = " " + Para_cron + ";" + (StartTime_HH + i) + ";" + "0-" + StopTime_mm;
                                continue;
                            }
                            else
                            {
                                //CronCommand = CronCommand + " " + Para_cron + ";" + (StartTime_HH + i) + ";" + "0-59";
                                Middle = " " + Para_cron + ";" + (StartTime_HH + 1) + "-" + (StopTime_HH - 1) + ";" + "0-59";
                            }

                        }

                        CronCommand = First + Middle + Last;
                    }
                }
                else
                {
                    //Ex:時間上17:00 ~ 2:00
                    int Interval = 0;
                    string First = string.Empty;
                    string Middle = string.Empty;
                    string Middle2 = string.Empty;
                    string Last = string.Empty;

                    #region 先拆成24:00以前
                    Interval = 23 - StartTime_HH;
                    for (int i = 0; i <= Interval; i++)
                    {
                        if (i == 0)
                        {
                            First = Para_cron + ";" + (StartTime_HH + i) + ";" + StartTime_mm + "-59";
                            continue;
                        }
                        else
                        {
                            Middle = " " + Para_cron + ";" + (StartTime_HH + 1) + "-23" + ";" + "0-59";
                            break;
                        }
                    }
                    #endregion

                    #region 再計算00:00~StopTime_HH:StopTime_mm
                    Interval = StopTime_HH - 0;
                    for (int j = 0; j <= Interval; j++)
                    {
                        if (j == Interval)
                        {
                            Last = " " + Para_cron + ";" + StopTime_HH + ";" + "0-" + StopTime_mm;
                            break;
                        }
                        else
                        {
                            Middle2 = " " + Para_cron + ";" + "0-" + (StopTime_HH - 1) + ";" + "0-59";
                            continue;
                        }
                    }
                    #endregion

                    CronCommand = First + Middle + Middle2 + Last;
                }
            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }

            //CronCommand = CronCommand.Substring(1).Trim();
            
            return CronCommand;
        }

        ///<summary>
        /// 取得已經加入電錶群組的電錶ID By scent at 2011/8/10
        ///</summary>
        public DataTable GetGroupInformation()
        {
            DataTable dt = new DataTable();

            try
            {
                string strSQL = "SELECT _id as GroupID,_name as GroupName,'' as DeviceNames FROM c_devicegroup_head";
                dt = new CommonFun().GenDataTable2(strSQL);

                DataTable dt2 = new DataTable();
                strSQL = "SELECT c_devicegroup_body.*,c_device._name FROM c_devicegroup_body LEFT JOIN c_device ON c_devicegroup_body._device_id = c_device._id";
                dt2 = new CommonFun().GenDataTable2(strSQL);

                foreach (DataRow row in dt.Rows)
                {
                    int GroupID = Convert.ToInt32(row["GroupID"]);
                    string DeviceNames = string.Empty;

                    foreach (DataRow row2 in dt2.Rows)
                    {
                        int GroupID2 = Convert.ToInt32(row2["_devicegroup_id"]) ;
                        if (GroupID == GroupID2)
                        {
                            DeviceNames = DeviceNames + "," + Convert.ToString(row2["_name"]);
                        }
                    }

                    DeviceNames = DeviceNames.Substring(1);

                    row["DeviceNames"] = DeviceNames;
                }

            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }

            return dt;
        }

        ///<summary>
        /// 取得查詢時間的需量報表 By scent at 2011/8/1
        ///</summary>
        public DataTable GetDemandReport(string Para_DeviceIDs, DateTime Para_StarTime, DateTime Para_EndTime)
        {
            DataTable dt = new DataTable();
            string strSQL = string.Empty;
            string TagIDs = string.Empty;

            try
            {
                #region 先取得主要的DataTable的架構
                strSQL = "SELECT * FROM" ;
                strSQL = strSQL + " (SELECT _id as DeviceID,_name as DeviceName FROM c_device WHERE _id IN (" + Para_DeviceIDs + ")) as UP_Device" ;
                strSQL = strSQL + " LEFT JOIN";
                strSQL = strSQL + " (SELECT _id as TagID,_device_id FROM c_tag WHERE _type = 2) as UP_Tag";
                strSQL = strSQL + " ON UP_Device.DeviceID = UP_Tag._device_id";

                DataTable temp = new DataTable();
                temp = new CommonFun().GenDataTable2(strSQL);

                if (temp.Rows.Count == 0)
                    throw new Exception("c_device與c_tag LEFT JOIN失敗");

                dt.Columns.Add("日期時間", typeof(string));
                
                for (int i = 0; i < temp.Rows.Count; i++)
                {
                    TagIDs = TagIDs + "," + Convert.ToString(temp.Rows[i]["TagID"]);
                    dt.Columns.Add(Convert.ToString(temp.Rows[i]["DeviceName"]), typeof(string));
                }

                TagIDs = TagIDs.Substring(1);
                #endregion
                
                #region 取得會用到的TagID資料表與資料
                
                string TableNames = string.Empty;
                DateTime TempSTime = new DateTime(Para_StarTime.Year,Para_StarTime.Month,Para_StarTime.Day,0,0,0) ;
                DateTime TempETime = new DateTime(Para_EndTime.Year,Para_EndTime.Month,Para_EndTime.Day,0,0,0) ;
                string YearMonth = string.Empty;
                bool Contine = true ;
                DataTable DT_etldata = new DataTable();
                DataTable DT_etlvalue = new DataTable();

                do
                {
                    if (YearMonth != TempSTime.ToString("yyyyMM"))
                    {
                        TableNames = "," + "'l_tag_etldata_" + TempSTime.ToString("yyyyMM") + "'";
                        YearMonth = TempSTime.ToString("yyyyMM");
                    }

                    TempSTime = TempSTime.AddDays(1);
                    if (DateTime.Compare(TempSTime, TempETime) > 0)
                        Contine = false;
                }
                while (Contine);

                TableNames = TableNames.Substring(1);

                strSQL = "SELECT Name FROM dbo.sysobjects WHERE Name IN (" + TableNames + ")";

                DT_etldata = new CommonFun().GenDataTable3(strSQL);

                if (DT_etldata.Rows.Count > 0)
                {
                    foreach (DataRow row in DT_etldata.Rows)
                    {
                        strSQL = "union SELECT _device_id as DeviceID,_tag_id as TagID,_start_time as STime,_end_time as ETime,_average as DemandValue FROM " + Convert.ToString(row["Name"]) + " WHERE (_tag_id IN (" + TagIDs + ")) AND (_start_time >= '" + Para_StarTime.ToString("yyyy/MM/dd HH:mm:ss") + ".000' AND _start_time <= '" + Para_EndTime.ToString("yyyy/MM/dd HH:mm:ss") + ".997')";
                    }

                    strSQL = strSQL.Substring(5);
                                        
                    DT_etlvalue = new CommonFun().GenDataTable3(strSQL);
                    
                }
                #endregion

                #region 開始進行組最終階段的DataTable

                ArrayList Date = new ArrayList();
                DateTime STime = new DateTime(Para_StarTime.Year, Para_StarTime.Month, Para_StarTime.Day, 0, 0, 0);
                DateTime ETime = new DateTime(Para_EndTime.Year, Para_EndTime.Month, Para_EndTime.Day, 0, 0, 0);
                string[] Max = new string[temp.Rows.Count];
                string[] Min = new string[temp.Rows.Count];
                int[] Count = new int[temp.Rows.Count];
                string[] Sum = new string[temp.Rows.Count];

                do
                {
                    DateTime tempDT = new DateTime(STime.Year, STime.Month, STime.Day, 0, 0, 0);
                    string strDate = tempDT.ToString("yyyy/MM/dd", System.Globalization.DateTimeFormatInfo.InvariantInfo);

                    for (int k = 0; k < 96; k++)
                    {
                        Date.Add(strDate + " " + tempDT.ToString("HH:mm"));
                        tempDT = tempDT.AddMinutes(15.0);
                    }

                    STime = STime.AddDays(1);
                }
                while (DateTime.Compare(STime, ETime) <= 0);

                for (int i = 0; i < Date.Count; i++)
                {
                    DataRow tempRow = dt.NewRow();
                    tempRow["日期時間"] = Convert.ToString(Date[i]);

                    for (int j = 0; j < temp.Rows.Count; j++)
                    {
                        int TagID = Convert.ToInt32(temp.Rows[j]["TagID"]);
                        string start_time = Convert.ToString(Date[i]) + ":00.000";
                        DataRow[] FilterRow = new DataRow[] { };
                        
                        if (DT_etlvalue.Rows.Count >0)
                            FilterRow = DT_etlvalue.Select("TagID=" + TagID + " AND STime='" + start_time + "'");

                        if (FilterRow.Length > 0)
                        {
                            string Value = Convert.ToString(System.Math.Round(Convert.ToDecimal(FilterRow[0]["DemandValue"]), 4));
                            tempRow[Convert.ToString(temp.Rows[j]["DeviceName"])] = Value;

                            #region 計數項目
                            Count[j] = Count[j] + 1;
                            #endregion

                            #region 總合
                            if (Sum[j] == string.Empty || Sum[j] == null)
                                Sum[j] = Value;
                            else
                                Sum[j] = Convert.ToString(Convert.ToDecimal(Sum[j]) + Convert.ToDecimal(Value));
                            #endregion

                            #region 最大
                            if (Max[j] == string.Empty || Max[j] == null)
                            {
                                Max[j] = Value + " (" + Convert.ToString(Date[i]) + ")";
                            }
                            else
                            {
                                decimal MaxValue = Convert.ToDecimal(Max[j].Substring(0, Max[j].IndexOf("(") - 1));
                                string MaxTime = Max[j].Substring(Max[j].IndexOf("(") + 1, Max[j].IndexOf(")") - (Max[j].IndexOf("(") + 1));

                                if (MaxValue == Convert.ToDecimal(Value))
                                {
                                    MaxTime = MaxTime + "," + Convert.ToString(Date[i]);
                                }
                                else if (MaxValue < Convert.ToDecimal(Value))
                                {
                                    MaxValue = Convert.ToDecimal(Value);
                                    MaxTime = Convert.ToString(Date[i]);
                                }

                                Max[j] = Convert.ToString(MaxValue) + " (" + MaxTime + ")";
                            }
                            #endregion

                            #region 最小
                            if (Min[j] == string.Empty || Min[j] == null)
                            {
                                Min[j] = Value + " (" + Convert.ToString(Date[i]) + ")";
                            }
                            else
                            {
                                decimal MinValue = Convert.ToDecimal(Min[j].Substring(0, Min[j].IndexOf("(") - 1));
                                string MinTime = Min[j].Substring(Min[j].IndexOf("(") + 1, Min[j].IndexOf(")") - (Min[j].IndexOf("(") + 1));

                                if (MinValue == Convert.ToDecimal(Value))
                                {
                                    MinTime = MinTime + "," + Convert.ToString(Date[i]);
                                }
                                else if (MinValue > Convert.ToDecimal(Value))
                                {
                                    MinValue = Convert.ToDecimal(Value);
                                    MinTime = Convert.ToString(Date[i]);
                                }

                                Min[j] = Convert.ToString(MinValue) + " (" + MinTime + ")";
                            }
                            #endregion
                        }
                    }

                    dt.Rows.Add(tempRow);
                }

                #region 插入最大值一列
                DataRow MaxRow = dt.NewRow();
                MaxRow["日期時間"] = "最大值";
                for (int m = 0; m < temp.Rows.Count; m++)
                {
                    MaxRow[Convert.ToString(temp.Rows[m]["DeviceName"])] = Max[m];
                }
                dt.Rows.Add(MaxRow);
                #endregion

                #region 插入最小值一列
                DataRow MinRow = dt.NewRow();
                MinRow["日期時間"] = "最小值";
                for (int m = 0; m < temp.Rows.Count; m++)
                {
                    MinRow[Convert.ToString(temp.Rows[m]["DeviceName"])] = Min[m];
                }
                dt.Rows.Add(MinRow);
                #endregion

                #region 插入平均值一列
                DataRow AvgRow = dt.NewRow();
                AvgRow["日期時間"] = "平均值";
                for (int m = 0; m < temp.Rows.Count; m++)
                {
                    if (Count[m] != 0 && (Sum[m] != string.Empty && Sum[m] != null))
                    {
                        decimal AvgValue = Convert.ToDecimal(Sum[m]) / Count[m];
                        AvgRow[Convert.ToString(temp.Rows[m]["DeviceName"])] = Convert.ToString(System.Math.Round(AvgValue, 4));
                    }
                }
                dt.Rows.Add(AvgRow);
                #endregion

                #region 插入總合一列
                DataRow SumRow = dt.NewRow();
                SumRow["日期時間"] = "總合";
                for (int m = 0; m < temp.Rows.Count; m++)
                {
                    SumRow[Convert.ToString(temp.Rows[m]["DeviceName"])] = Sum[m];
                }
                dt.Rows.Add(SumRow);
                #endregion

                #endregion
                
            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }


            return dt;
        }

        ///<summary>
        /// 動態產出rdlc檔 By scent at 2011/8/4
        ///</summary>
        public string OutputRDLC(DataTable Para_DT)
        {
            string Path = string.Empty;
            StringBuilder xml = new StringBuilder();
            try
            {
                if (Para_DT.Columns.Count == 0)
                    throw new Exception("產出RDLC異常，因為報表來源錯誤");

                #region 開始製作rdlc XML文件
                xml.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>") ;
                xml.Append("<Report xmlns:rd=\"http://schemas.microsoft.com/SQLServer/reporting/reportdesigner\" xmlns=\"http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition\">");
                xml.Append("<DataSources>");
                xml.Append("<DataSource Name=\"ReportDesign\">");
                xml.Append("<ConnectionProperties>");
                xml.Append("<DataProvider>System.Data.DataSet</DataProvider>");
                xml.Append("<ConnectString>/* Local Connection */</ConnectString>");
                xml.Append("</ConnectionProperties>");
                xml.Append("<rd:DataSourceID>1b8f3166-8628-4974-a894-77266e5faadb</rd:DataSourceID>");
                xml.Append("</DataSource>");
                xml.Append("</DataSources>");
                xml.Append("<DataSets>");
                xml.Append("<DataSet Name=\"DataSet_Data\">");
                xml.Append("<Fields>");
                foreach (DataColumn col in Para_DT.Columns)
                {
                    xml.Append("<Field Name=\""+col.ColumnName+"\">");
                    xml.Append("<DataField>" + col.ColumnName + "</DataField>");
                    xml.Append("<rd:TypeName>System.String</rd:TypeName>");
                    xml.Append("</Field>");
                }
                xml.Append("</Fields>");
                xml.Append("<Query>");
                xml.Append("<DataSourceName>ReportDesign</DataSourceName>");
                xml.Append("<CommandText>/* Local Query */</CommandText>");
                xml.Append("</Query>");
                xml.Append("<rd:DataSetInfo>");
                xml.Append("<rd:DataSetName>DataSet_Data</rd:DataSetName>");
                xml.Append("<rd:TableName>Data</rd:TableName>");
                xml.Append("</rd:DataSetInfo>");
                xml.Append("</DataSet>");
                xml.Append("</DataSets>");
                #region RDLC 本體
                xml.Append("<Body>");
                xml.Append("<ReportItems>");
                xml.Append("<Tablix Name=\"Tablix1\">");
                xml.Append("<TablixBody>");
                xml.Append("<TablixColumns>");
                foreach (DataColumn col in Para_DT.Columns)
                {
                    xml.Append("<TablixColumn>");
                    //xml.Append("<Width>2.19271in</Width>");
                    xml.Append("<Width>" + System.Math.Round((15.54426 / Para_DT.Columns.Count), 4) + "cm</Width>");
                    xml.Append("</TablixColumn>");
                }
                xml.Append("</TablixColumns>");
                xml.Append("<TablixRows>");
                xml.Append("<TablixRow>");
                xml.Append("<Height>0.23622in</Height>");
                xml.Append("<TablixCells>");
                foreach (DataColumn col in Para_DT.Columns)
                {
                    xml.Append("<TablixCell>");
                    xml.Append("<CellContents>");
                    xml.Append("<Textbox Name=\"" + col.ColumnName + "\">");
                    xml.Append("<CanGrow>true</CanGrow>");
                    xml.Append("<KeepTogether>true</KeepTogether>");
                    xml.Append("<Paragraphs>");
                    xml.Append("<Paragraph>");
                    xml.Append("<TextRuns>");
                    xml.Append("<TextRun>");
                    xml.Append("<Value>" + col.ColumnName + "</Value>");
                    xml.Append("<Style>");
                    xml.Append("<FontStyle>Normal</FontStyle>");
                    xml.Append("<FontFamily>標楷體</FontFamily>");
                    xml.Append("<FontSize>12pt</FontSize>");
                    xml.Append("<FontWeight>Bold</FontWeight>");
                    xml.Append("<TextDecoration>None</TextDecoration>");
                    xml.Append("<Color>#000000</Color>");
                    xml.Append("</Style>");
                    xml.Append("</TextRun>");
                    xml.Append("</TextRuns>");
                    xml.Append("<Style>");
                    xml.Append("<TextAlign>Center</TextAlign>");
                    xml.Append("</Style>");
                    xml.Append("</Paragraph>");
                    xml.Append("</Paragraphs>");
                    xml.Append("<rd:DefaultName>Textbox27</rd:DefaultName>");
                    xml.Append("<ZIndex>4</ZIndex>");
                    xml.Append("<Style>");
                    xml.Append("<Border>");
                    xml.Append("<Style>Solid</Style>");
                    xml.Append("</Border>");
                    xml.Append("<BackgroundColor>Yellow</BackgroundColor>");
                    xml.Append("<PaddingLeft>2pt</PaddingLeft>");
                    xml.Append("<PaddingRight>2pt</PaddingRight>");
                    xml.Append("<PaddingTop>2pt</PaddingTop>");
                    xml.Append("<PaddingBottom>2pt</PaddingBottom>");
                    xml.Append("</Style>");
                    xml.Append("</Textbox>");
                    xml.Append("</CellContents>");
                    xml.Append("</TablixCell>");
                }
                xml.Append("</TablixCells>");
                xml.Append("</TablixRow>");
                xml.Append("<TablixRow>");
                xml.Append("<Height>0.23622in</Height>");
                xml.Append("<TablixCells>");
                foreach (DataColumn col in Para_DT.Columns)
                {
                    xml.Append("<TablixCell>");
                    xml.Append("<CellContents>");
                    xml.Append("<Textbox Name=\"ID_" + col.ColumnName + "\">");
                    xml.Append("<CanGrow>true</CanGrow>");
                    xml.Append("<KeepTogether>true</KeepTogether>");
                    xml.Append("<Paragraphs>");
                    xml.Append("<Paragraph>");
                    xml.Append("<TextRuns>");
                    xml.Append("<TextRun>");
                    xml.Append("<Value>=Fields!" + col.ColumnName + ".Value</Value>");
                    xml.Append("<Style />");
                    xml.Append("</TextRun>");
                    xml.Append("</TextRuns>");
                    xml.Append("<Style>");
                    xml.Append("<TextAlign>Left</TextAlign>");
                    xml.Append("</Style>");
                    xml.Append("</Paragraph>");
                    xml.Append("</Paragraphs>");
                    xml.Append("<rd:DefaultName>ID_datetime</rd:DefaultName>");
                    xml.Append("<Style>");
                    xml.Append("<Border>");
                    xml.Append("<Color>LightGrey</Color>");
                    xml.Append("<Style>Solid</Style>");
                    xml.Append("</Border>");
                    xml.Append("<PaddingLeft>2pt</PaddingLeft>");
                    xml.Append("<PaddingRight>2pt</PaddingRight>");
                    xml.Append("<PaddingTop>2pt</PaddingTop>");
                    xml.Append("<PaddingBottom>2pt</PaddingBottom>");
                    xml.Append("</Style>");
                    xml.Append("</Textbox>");
                    xml.Append("</CellContents>");
                    xml.Append("</TablixCell>");
                }
                xml.Append("</TablixCells>");
                xml.Append("</TablixRow>");
                xml.Append("</TablixRows>");
                xml.Append("</TablixBody>");
                xml.Append("<TablixColumnHierarchy>");
                xml.Append("<TablixMembers>");
                foreach (DataColumn col in Para_DT.Columns)
                {
                    xml.Append("<TablixMember />");
                }
                xml.Append("</TablixMembers>");
                xml.Append("</TablixColumnHierarchy>");
                xml.Append("<TablixRowHierarchy>");
                xml.Append("<TablixMembers>");
                xml.Append("<TablixMember>");
                xml.Append("<KeepWithGroup>After</KeepWithGroup>");
                xml.Append("</TablixMember>");
                xml.Append("<TablixMember>");
                xml.Append("<Group Name=\"詳細資料\" />");
                xml.Append("</TablixMember>");
                xml.Append("</TablixMembers>");
                xml.Append("</TablixRowHierarchy>");
                xml.Append("<RepeatColumnHeaders>true</RepeatColumnHeaders>");
                xml.Append("<Left>0.37084cm</Left>");
                xml.Append("<Height>1.2cm</Height>");
                //xml.Append("<Width>5.56948cm</Width>");
                xml.Append("<Width>15.54426cm</Width>");
                xml.Append("<Style>");
                xml.Append("<Border>");
                xml.Append("<Style>None</Style>");
                xml.Append("</Border>");
                xml.Append("</Style>");
                xml.Append("</Tablix>");
                xml.Append("</ReportItems>");
                xml.Append("<Height>0.83366in</Height>");
                xml.Append("<Style />");
                xml.Append("</Body>");
                #endregion
                xml.Append("<ReportParameters>");
                xml.Append("<ReportParameter Name=\"Report_Parameter_Date\">");
                xml.Append("<DataType>String</DataType>");
                xml.Append("<Prompt>ReportParameter1</Prompt>");
                xml.Append("</ReportParameter>");
                xml.Append("<ReportParameter Name=\"Report_Parameter_ReportName\">");
                xml.Append("<DataType>String</DataType>");
                xml.Append("<Prompt>ReportParameter1</Prompt>");
                xml.Append("</ReportParameter>");
                xml.Append("<ReportParameter Name=\"Report_Parameter_Room\">") ;
                xml.Append("<DataType>String</DataType>");
                xml.Append("<Prompt>ReportParameter1</Prompt>");
                xml.Append("</ReportParameter>");
                xml.Append("<ReportParameter Name=\"Report_Parameter_TotalPower\">");
                xml.Append("<DataType>String</DataType>");
                xml.Append("<Prompt>ReportParameter1</Prompt>");
                xml.Append("</ReportParameter>");
                xml.Append("<ReportParameter Name=\"Report_Parameter_TotalMoney\">") ;
                xml.Append("<DataType>String</DataType>");
                xml.Append("<Prompt>ReportParameter1</Prompt>");
                xml.Append("</ReportParameter>");
                xml.Append("<ReportParameter Name=\"Report_Parameter_Column\">") ;
                xml.Append("<DataType>String</DataType>");
                xml.Append("<Prompt>ReportParameter1</Prompt>");
                xml.Append("</ReportParameter>");
                xml.Append("</ReportParameters>");
                xml.Append("<Width>6.5in</Width>");
                xml.Append("<Page>");
                #region Page Header
                xml.Append("<PageHeader>");
                xml.Append("<Height>2.43022cm</Height>");
                xml.Append("<PrintOnFirstPage>true</PrintOnFirstPage>");
                xml.Append("<PrintOnLastPage>true</PrintOnLastPage>");
                xml.Append("<ReportItems>");
                xml.Append("<Textbox Name=\"textbox1\">");
                xml.Append("<CanGrow>true</CanGrow>");
                xml.Append("<KeepTogether>true</KeepTogether>");
                xml.Append("<Paragraphs>");
                xml.Append("<Paragraph>");
                xml.Append("<TextRuns>");
                xml.Append("<TextRun>");
                xml.Append("<Value>=Parameters!Report_Parameter_ReportName.Value</Value>");
                xml.Append("<Style>");
                xml.Append("<FontFamily>標楷體</FontFamily>");
                xml.Append("<FontSize>16pt</FontSize>");
                xml.Append("<FontWeight>Bold</FontWeight>");
                xml.Append("</Style>");
                xml.Append("</TextRun>");
                xml.Append("</TextRuns>");
                xml.Append("<Style />");
                xml.Append("</Paragraph>");
                xml.Append("</Paragraphs>");
                xml.Append("<rd:DefaultName>textbox1</rd:DefaultName>");
                xml.Append("<Top>0.31292cm</Top>");
                xml.Append("<Left>0.37084cm</Left>");
                xml.Append("<Height>0.75cm</Height>");
                xml.Append("<Width>10.74125cm</Width>");
                xml.Append("<Style>");
                xml.Append("<PaddingLeft>2pt</PaddingLeft>");
                xml.Append("<PaddingRight>2pt</PaddingRight>");
                xml.Append("<PaddingTop>2pt</PaddingTop>");
                xml.Append("<PaddingBottom>2pt</PaddingBottom>");
                xml.Append("</Style>");
                xml.Append("</Textbox>");
                xml.Append("<Textbox Name=\"Textbox24\">");
                xml.Append("<CanGrow>true</CanGrow>");
                xml.Append("<KeepTogether>true</KeepTogether>");
                xml.Append("<Paragraphs>");
                xml.Append("<Paragraph>");
                xml.Append("<TextRuns>");
                xml.Append("<TextRun>");
                xml.Append("<Value>=\"頁次： \" &amp; Globals!PageNumber &amp; \" / \"  &amp; Globals!TotalPages</Value>");
                xml.Append("<Style>");
                xml.Append("<FontStyle>Normal</FontStyle>");
                xml.Append("<FontFamily>標楷體</FontFamily>");
                xml.Append("<FontSize>12pt</FontSize>");
                xml.Append("<FontWeight>Bold</FontWeight>");
                xml.Append("<TextDecoration>None</TextDecoration>");
                xml.Append("</Style>");
                xml.Append("</TextRun>");
                xml.Append("</TextRuns>");
                xml.Append("<Style>");
                xml.Append("<TextAlign>Right</TextAlign>");
                xml.Append("</Style>");
                xml.Append("</Paragraph>");
                xml.Append("</Paragraphs>");
                xml.Append("<rd:DefaultName>Textbox24</rd:DefaultName>");
                xml.Append("<Top>0.31292cm</Top>");
                xml.Append("<Left>12.19802cm</Left>");
                xml.Append("<Height>0.74723cm</Height>");
                xml.Append("<Width>3.71708cm</Width>");
                xml.Append("<ZIndex>1</ZIndex>");
                xml.Append("<Style>");
                xml.Append("<Border>");
                xml.Append("<Style>None</Style>");
                xml.Append("</Border>");
                xml.Append("<PaddingLeft>2pt</PaddingLeft>");
                xml.Append("<PaddingRight>2pt</PaddingRight>");
                xml.Append("<PaddingTop>2pt</PaddingTop>");
                xml.Append("<PaddingBottom>2pt</PaddingBottom>");
                xml.Append("</Style>");
                xml.Append("</Textbox>");
                xml.Append("<Textbox Name=\"textbox6\">");
                xml.Append("<CanGrow>true</CanGrow>");
                xml.Append("<KeepTogether>true</KeepTogether>");
                xml.Append("<Paragraphs>");
                xml.Append("<Paragraph>");
                xml.Append("<TextRuns>");
                xml.Append("<TextRun>");
                xml.Append("<Value>=Parameters!Report_Parameter_Room.Value</Value>");
                xml.Append("<Style>");
                xml.Append("<FontFamily>標楷體</FontFamily>");
                xml.Append("<FontSize>12pt</FontSize>");
                xml.Append("</Style>");
                xml.Append("</TextRun>");
                xml.Append("</TextRuns>");
                xml.Append("<Style />");
                xml.Append("</Paragraph>");
                xml.Append("</Paragraphs>");
                xml.Append("<rd:DefaultName>textbox2</rd:DefaultName>");
                xml.Append("<Top>1.51274cm</Top>");
                xml.Append("<Left>0.37084cm</Left>");
                xml.Append("<Height>0.63492cm</Height>");
                xml.Append("<Width>6.63979cm</Width>");
                xml.Append("<ZIndex>2</ZIndex>");
                xml.Append("<Style>");
                xml.Append("<PaddingLeft>2pt</PaddingLeft>");
                xml.Append("<PaddingRight>2pt</PaddingRight>");
                xml.Append("<PaddingTop>2pt</PaddingTop>");
                xml.Append("<PaddingBottom>2pt</PaddingBottom>");
                xml.Append("</Style>");
                xml.Append("</Textbox>");
                xml.Append("<Textbox Name=\"textbox2\">");
                xml.Append("<CanGrow>true</CanGrow>");
                xml.Append("<KeepTogether>true</KeepTogether>");
                xml.Append("<Paragraphs>");
                xml.Append("<Paragraph>");
                xml.Append("<TextRuns>");
                xml.Append("<TextRun>");
                xml.Append("<Value>=Parameters!Report_Parameter_Date.Value</Value>");
                xml.Append("<Style>");
                xml.Append("<FontFamily>標楷體</FontFamily>");
                xml.Append("<FontSize>12pt</FontSize>");
                xml.Append("</Style>");
                xml.Append("</TextRun>");
                xml.Append("</TextRuns>");
                xml.Append("<Style>");
                xml.Append("<TextAlign>Right</TextAlign>");
                xml.Append("</Style>");
                xml.Append("</Paragraph>");
                xml.Append("</Paragraphs>");
                xml.Append("<rd:DefaultName>textbox2</rd:DefaultName>");
                xml.Append("<Top>1.51274cm</Top>");
                xml.Append("<Left>7.55344cm</Left>");
                xml.Append("<Height>0.63492cm</Height>");
                xml.Append("<Width>8.36166cm</Width>");
                xml.Append("<ZIndex>3</ZIndex>");
                xml.Append("<Style>");
                xml.Append("<PaddingLeft>2pt</PaddingLeft>");
                xml.Append("<PaddingRight>2pt</PaddingRight>");
                xml.Append("<PaddingTop>2pt</PaddingTop>");
                xml.Append("<PaddingBottom>2pt</PaddingBottom>");
                xml.Append("</Style>");
                xml.Append("</Textbox>");
                xml.Append("</ReportItems>");
                xml.Append("<Style>");
                xml.Append("<Border>");
                xml.Append("<Style>None</Style>");
                xml.Append("</Border>");
                xml.Append("</Style>");
                xml.Append("</PageHeader>");
                #endregion
                xml.Append("<PageHeight>29.7cm</PageHeight>");
                xml.Append("<PageWidth>21cm</PageWidth>");
                xml.Append("<LeftMargin>2cm</LeftMargin>");
                xml.Append("<RightMargin>2cm</RightMargin>");
                xml.Append("<TopMargin>2cm</TopMargin>");
                xml.Append("<BottomMargin>2cm</BottomMargin>");
                xml.Append("<ColumnSpacing>0.13cm</ColumnSpacing>");
                xml.Append("<Style />");
                xml.Append("</Page>");
                xml.Append("<rd:ReportID>fd0ff8a4-4669-4ca1-9408-abe91b930f38</rd:ReportID>");
                xml.Append("<rd:ReportUnitType>Cm</rd:ReportUnitType>");
                xml.Append("</Report>");
                #endregion

                #region 將動態產生的RDLC XML檔，寫回暫存目錄
                
                string tempPath = @"Report/Temp/" + System.Guid.NewGuid().ToString() + @"/";
                System.IO.DirectoryInfo myDir = new System.IO.DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory + tempPath.Replace(@"/", @"\"));
                if (!myDir.Exists)
                    myDir.Create();

                System.IO.StreamWriter sw = new System.IO.StreamWriter(myDir.FullName + @"\" + "DemandReport.rdlc", false, Encoding.UTF8) ;
                sw.WriteLine(xml.ToString());
                sw.Close();

                Path = tempPath.Replace(@"/", @"\") + @"\" + "DemandReport.rdlc";

                #endregion

                _ErrMsg = string.Empty;
            }
            catch (Exception Ex)
            {
                _ErrMsg = Ex.Message;
            }

            return Path;
        }
    }
}

